package com.cn.lycss.dao.impl;

import com.cn.lycss.dao.UserDao;
import com.cn.lycss.model.PageModel;
import com.cn.lycss.model.UserModel;
import com.cn.lycss.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {
    @Override
    public UserModel findByUserNameAndPassword(UserModel userModel) {
//        拼接sql语句
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
//        通过调用工具类JDBC查询数据库，得到结果集
        ResultSet resultSet = JDBC.query(sql);
//        创建一个空值对象
        UserModel model = null;
        try {
//            如果结果集里面有值，则进入while循环
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql="select * from user where 1=1";
       sql+=setSql(userModel);
//select *from user where username=?;
        sql+=" order by id desc";
        sql += " limit " + (userModel.getPageNumber() - 1) * +userModel.getPageSize() + ", " + userModel.getPageSize() + ";";

        ResultSet resultSet=JDBC.query(sql);
        List<UserModel> list=new ArrayList<>();
        try {
            while (resultSet.next()){
            UserModel model=new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEnable(resultSet.getInt("enable"));
                model.setAddress(resultSet.getString("address"));
                model.setEmail(resultSet.getString("email"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`," +
                " `phone`, `sex`, `update_time`," +
                " `hobby`, `birthday`, `remark`) VALUES (" +
                "'" + userModel.getUsername() + "', '" + userModel.getAddress() + "', now()," +
                " '" + userModel.getEmail() + "', '" + userModel.getEnable() + "', '" + userModel.getPassword() + "'," +
                " '" + userModel.getPhone() + "', '" + userModel.getSex() + "', now(), " +
                "'" + userModel.getHobby() + "', '" + userModel.getBirthday() + "', '');\n";
        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET " ;
        if (userModel.getUsername()!=null&&!"".equals(userModel.getUsername()))
        {
           sql+= "`username` = '" + userModel.getUsername() + "', ";
        }

        if (userModel.getAddress() != null) {
            sql += "`address` = '" + userModel.getAddress() + "', ";
        }
        if (userModel.getEmail() != null) {
            sql += "`email` = '" + userModel.getEmail() + "', ";
        }
        if (userModel.getEnable() != -1) {
            sql += " `enable` = '" + userModel.getEnable() + "',";
        }
        if (userModel.getPhone() != null&&!"".equals(userModel.getUsername())) {
            sql += "`phone` = '" + userModel.getPhone() + "', " ;
        }
        if (userModel.getSex() != null&&!"".equals(userModel.getUsername())) {
            sql += "`sex` = '" + userModel.getSex() + "', ";
        }
        if (userModel.getPassword() != null&&!"".equals(userModel.getUsername())) {
            sql += "`password` = '" + userModel.getPassword() + "', " ;
        }
        if (userModel.getHobby() != null&&!"".equals(userModel.getUsername())) {
            sql += " `hobby` = '" + userModel.getHobby() + "', "  ;
        }
        if (userModel.getBirthday() != null&&!"".equals(userModel.getUsername())) {
            sql += "`birthday` = '" + userModel.getBirthday() + "',"  ;
        }

        sql += "`update_time` = now()," +
                " `remark` = '' " +
                "WHERE (`id` = '" + userModel.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select * from user where id=" + userModel.getId();
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = new UserModel();
        try {
            while (resultSet.next()) {
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEnable(resultSet.getInt("enable"));
                model.setAddress(resultSet.getString("address"));
                model.setEmail(resultSet.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
//                String sql = "delete from user where id=?" ;
//        String sql1="select *from user where username=? and id=?";
//        List list=new ArrayList();
//        list.add(userModel.getUsername());
//        list.add(userModel.getId());
//        return JDBC.updatea(sql1,list);
        return JDBC.update(sql);
    }

    @Override
    public Integer getCount(UserModel userModel) {
        String sql = "select count(*) count from user where 1=1";
        sql+=setSql(userModel);
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    private String setSql(UserModel userModel){
        String sql="";
        if(userModel.getUsername()!=null&&!"".equals(userModel.getUsername())){
            sql+=" and username like '%"+userModel.getUsername()+"%'";
        }
        if(userModel.getPhone()!=null&&!"".equals(userModel.getPhone())){
            sql+="and phone like '%"+userModel.getPhone()+"%'";
        }
        if(userModel.getSex()!=null&&!"".equals(userModel.getSex())){
            sql+=" and sex like '%"+userModel.getSex()+"%'";
        }
        if(userModel.getStart()!=null&& userModel.getEnd()==null){
            sql+=" and birthday>='"+userModel.getStart()+"'";
        }
        if(userModel.getStart()==null&& userModel.getEnd()!=null){
            sql+=" and birthday<='"+userModel.getEnd()+"'";
        }
        if(userModel.getStart()==null&& userModel.getEnd()==null){
            sql+="";
        }
        if(userModel.getStart()!=null&& userModel.getEnd()!=null){
            sql+=" and birthday between'"+userModel.getStart()+"'and'"+userModel.getEnd()+"'";
        }
        if(userModel.getEnable()!=null&&userModel.getEnable()!=-1){
            sql+=" and enable="+userModel.getEnable();
        }
        if(userModel.getHobby()!=null&&!"".equals(userModel.getHobby())){
            String hobby =userModel.getHobby();
            String [] hobbys=hobby.split(",");
            sql+=" and (";
            for (int i = 0; i <hobbys.length ; i++) {
                if (i==0){
                    sql+=" find_in_set('"+hobbys[i]+"',`hobby`) ";
                }else {
                    sql+=" or find_in_set('"+hobbys[i]+"',`hobby`) ";
                }
            }
            sql+=")";
        }
        return sql;
    }

    public static void main(String[] args) {
       UserModel userModel=new UserModel();
       userModel.setPageNumber(1);
       userModel.setPageSize(10);
        new UserImpl().findAll(userModel);
    }
}